在資訊系統中,曆法是重要的環節.昨天我們使用了Python
來取得24節氣的資料.參考以下

https://ithelp.ithome.com.tw/articles/10230451

24節氣是由立春(編號0)開始到次年的大寒(編號23),所以要查詢
完整的農曆年對應節氣,是要產生兩個西元年份,再來統整.

若我們將產生的結果轉存到資料庫中,處理起來及後續應用會更方便.
既然是透過Python取得,一般會使用Python的資料庫driver將結果
存入資料庫中.
今天我們要使用另外的方式,在Postgresql中使用 Stored Procedure
,而且是用Python.就是 PLPython.

神雕俠侶中的金輪法王的龍象般若功,練成之後威力很好很強大.
Python 與 Postgresql 對應到龍象.

來看看怎樣使用吧.

在Postgresql中,每個使用者登入時都必須要到一個database,
database中可以視需要建立schema,以方便管理資料庫物件(table, function等等).

在此先建立一個 akifunc 的 schema,以方便管理 functions.

create schema akifunc;

因為 PLPython 與 Postgresql 之間, 資料型態
PG                      Python3
int2, int4              int
int8                    int
real,double,numeric     float
bytea                   bytes
text,char,varchar       str
其他型態                 str 

所以在此Python的 datetime 型態傳出來,我們使用了text,後續再轉型態.

create or replace function akifunc.solarterms (iyear int)
  returns table(tcycyear text, tsltrms text, iseq int, ttsz text)
as $_aki_$
  import eacal
 
  ct = eacal.EACal(zh_t=True)
  tcycyear = ct.get_cycle_year(iyear)
  terms = ct.get_annual_solar_terms(iyear)[2:] + ct.get_annual_solar_terms(iyear+1)[0:2]
    
  return [(tcycyear, tupl[0], tupl[1], tupl[2]) for tupl in terms]
$_aki_$ language plpython3u;

--
試試看查詢結果

select iyear
     , sol.*
  from generate_series(2019,2021) gs(iyear)
  join lateral 
       (select *
          from akifunc.solarterms(iyear)
       ) sol
       on true;

+-------+----------+---------+------+----------------------------------+
| iyear | tcycyear | tsltrms | iseq |               ttsz               |
+-------+----------+---------+------+----------------------------------+
|  2019 | 己亥     | 立春    |    0 | 2019-02-04 11:14:11.029273+08:00 |
|  2019 | 己亥     | 雨水    |    1 | 2019-02-19 07:03:50.211474+08:00 |
...
|  2019 | 己亥     | 小寒    |   22 | 2020-01-06 05:29:55.861494+08:00 |
|  2019 | 己亥     | 大寒    |   23 | 2020-01-20 22:54:28.653875+08:00 |
|  2020 | 庚子     | 立春    |    0 | 2020-02-04 17:03:09.399758+08:00 |
|  2020 | 庚子     | 雨水    |    1 | 2020-02-19 12:56:51.600730+08:00 |
|  2020 | 庚子     | 驚蟄    |    2 | 2020-03-05 10:56:46.578537+08:00 |
|  2020 | 庚子     | 春分    |    3 | 2020-03-20 11:49:32.896498+08:00 |
...
|  2021 | 辛丑     | 小寒    |   22 | 2022-01-05 17:13:49.823105+08:00 |
|  2021 | 辛丑     | 大寒    |   23 | 2022-01-20 10:38:52.179387+08:00 |
+-------+----------+---------+------+----------------------------------+
(72 rows)

在實際應用上,秒以下可以忽略.
所以後續建立 table時,將其精度先調整好.

create table solarterms (
  id int generated always as identity
, iyear int not null
, iseq smallint not null
, tcycyear text not null
, tsltrms text not null
, rtsz tstzrange not null
);

--
with t1 as (
select iyear
     , iseq
     , tcycyear
     , tsltrms
     , tstzrange(ttsz::timestamptz(0),
       lead(ttsz::timestamptz(0)) over(order by iyear, iseq),
       '[)')
  from generate_series(2019,2021) gs(iyear)
  join lateral 
       (select *
          from akifunc.solarterms(iyear)
       ) sol
       on true
)
insert into solarterms(iyear, iseq, tcycyear, tsltrms, rtsz)
select *
  from t1;
--
select *
  from solarterms;

+----+-------+------+----------+---------+-----------------------------------------------------+
| id | iyear | iseq | tcycyear | tsltrms |                        rtsz                         |
+----+-------+------+----------+---------+-----------------------------------------------------+
|  1 |  2019 |    0 | 己亥     | 立春    | ["2019-02-04 11:14:11+08","2019-02-19 07:03:50+08") |
|  2 |  2019 |    1 | 己亥     | 雨水    | ["2019-02-19 07:03:50+08","2019-03-06 05:09:40+08") |
|  3 |  2019 |    2 | 己亥     | 驚蟄    | ["2019-03-06 05:09:40+08","2019-03-21 05:58:23+08") |
...
| 25 |  2020 |    0 | 庚子     | 立春    | ["2020-02-04 17:03:09+08","2020-02-19 12:56:52+08") |
| 26 |  2020 |    1 | 庚子     | 雨水    | ["2020-02-19 12:56:52+08","2020-03-05 10:56:47+08") |
| 27 |  2020 |    2 | 庚子     | 驚蟄    | ["2020-03-05 10:56:47+08","2020-03-20 11:49:33+08") |
| 28 |  2020 |    3 | 庚子     | 春分    | ["2020-03-20 11:49:33+08","2020-04-04 15:38:09+08") |
| 29 |  2020 |    4 | 庚子     | 清明    | ["2020-04-04 15:38:09+08","2020-04-19 22:45:28+08") |
...
| 71 |  2021 |   22 | 辛丑     | 小寒    | ["2022-01-05 17:13:50+08","2022-01-20 10:38:52+08") |
| 72 |  2021 |   23 | 辛丑     | 大寒    | ["2022-01-20 10:38:52+08",)                         |
+----+-------+------+----------+---------+-----------------------------------------------------+
(72 rows)

Time: 0.904 ms

--------
後續可以使用 Postgresql 強大的 range type operator 來查詢.
例如可以用 包含於運算子 <@ 

select now()
     , tcycyear
     , tsltrms
     , rtsz 
  from solarterms
 where now() <@ rtsz;

+-------------------------------+----------+---------+-----------------------------------------------------+
|              now              | tcycyear | tsltrms |                        rtsz                         |
+-------------------------------+----------+---------+-----------------------------------------------------+
| 2020-03-17 12:19:27.806164+08 | 庚子     | 驚蟄    | ["2020-03-05 10:56:47+08","2020-03-20 11:49:33+08") |
+-------------------------------+----------+---------+-----------------------------------------------------+
(1 row)